﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;

namespace Theme_Creator
{
    class db
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        //Constructor
        public db()
        {
            Initialize();
        }

        //Initialize values
        private void Initialize()
        {
            server = "henriarends.nl";
            server = "195.211.72.42";
            database = "deb39525_wp1";
            uid = "deb39525_walid";
            password = "Geheim12";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);
        }


        //open connection to database
        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                }
                return false;
            }
        }

        //Close connection
        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        //Insert statement
        public void Insert(String titel, String auteur, String groep, String school_id)
        {
            int tempID = Convert.ToInt32(school_id);
            string query = "INSERT INTO tc_thema (titel, auteur, groep, school_id) VALUES('"+titel+"', '"+auteur+"', '"+groep+"','"+tempID+"')";
            
            //open connection
            if (this.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }
        //select statement
        protected String naam;
        protected String wachtwoord;
        protected String schoolID;
        
        private void getInfo()
        {
            naam = Form1.getNameSchool();
            wachtwoord = Form1.getPassWord();
            schoolID = Form1.getSchoolID();
        }
        public List<string> Select()
        {
            getInfo();
            string query = "SELECT * FROM tc_school WHERE naam = '"+naam+"' AND wachtwoord = '"+wachtwoord+"';";

            //Create a list to store the result
            List<string> list = new List<string>();


            //Open connection
            if (this.OpenConnection())
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    list.Add(dataReader["id"] + "");
                    list.Add(dataReader["naam"] + "");
                    list.Add(dataReader["wachtwoord"] + "");
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                this.CloseConnection();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }

        }
    }
}
